﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;


namespace KPIS.GERP.GFMS.DAL
{
    public sealed class D_FIM_Receives : IDAL_FIM_Receives
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public D_FIM_Receives()
        {

        }

        public IList<M_FIM_Receives> GetFull(Nullable<int> bgm_source_seq, Nullable<int> work_area_seq
            , Nullable<int> bgm_year)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Receives> infoList = new List<M_FIM_Receives>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(3, "@bgm_year", bgm_year);

                strSQL = "SELECT r.receive_seq, r.bgm_source_seq, r.work_area_seq, r.bgm_year, r.vat_amount, r.exclude_vat, r.vat_percent, r.include_vat"
                    + ", r.receive_type_seq, r.receipt_no, r.ref_no, r.receive_date, r.manual_receipt, r.receive_mode, r.method_seq"
                    + ", r.cheque_no, r.branch_seq, r.bank_acc_seq, r.contact_seq"
                    + ", r.emp_seq, r.temp_contact, r.amount, r.recive_remark, r.tax_percent, r.tax"
                    + ", r.received_by, r.remarks, r.tran_grp_seq, r.deposit_seq"
                    + ", r.petition_seq, r.money_req_seq, r.pay_seq, r.record_status, r.source, r.print_out"
                    + ", r.contact_address, r.contact_branch, r.contact_tax_code, r.temp_contact"
                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
	                    + " else case when r.contact_seq is not null then c.NameTh"
		                    + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
		                    + " end"
	                    + " end as payer_name"
                    + ", r.icm_bid_rec_seq, r.icm_bid_off_col_seq, r.icm_po_con_col_seq, r.icm_po_pre_col_seq"
                    + ", r.icm_dn_pen_seq, r.icm_po_instal_seq, r.icm_stockmm_seq"

                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', coalesce(mr.ref_no, ''))"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', coalesce(pt.ref_no, ''))"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"
                    + ", bb.bank_seq, b.bank_id, b.bank_name, bb.branch_id, bb.branch_name"
                    + ", acc.bank_acc_no, acc.bank_acc_name"

                    + ", s.bgm_source_name, area.work_area_name, c.NameTh"
                    + ", m.method_name"
                    + ", mr.event_seq"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", concat(recnp.prefix_name, rec.first_name, ' ', rec.last_name) as received_by_name"
                    + ", rs.record_status_name"
                    + ", r.created_by, r.created_when, r.updated_by, r.updated_when"
                    + " FROM fim_receives r"
                    //+ " LEFT JOIN (select receive_seq"
                    //    + ", sum(coalesce(bath_amount, 0)) as total_bath_amount"
                    //    + " from fim_receive_dtls"
                    //    + " where record_status not like 'D'"
                    //    + " group by receive_seq"
                    //    + ") rd on r.receive_seq = rd.receive_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_mas_methods m ON r.method_seq = m.method_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON r.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON r.bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN bgm_mas_source s ON r.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_ps_employees rec ON r.received_by = rec.emp_seq"
                    + " LEFT JOIN mas_name_prefixes recnp ON rec.name_prefix_seq = recnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON r.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN sys_record_status rs ON r.record_status = rs.record_status"
                    + " WHERE r.record_status not like 'D'"
                    + (bgm_source_seq != null ? " AND r.bgm_source_seq = @bgm_source_seq" : "")
                    + (work_area_seq != null ? " AND r.work_area_seq = @work_area_seq" : "")
                    + (bgm_year != null ? " AND r.bgm_year = @bgm_year" : "")
                    + " AND r.receive_mode not like '3' AND r.print_out = 0"
                    + " ORDER BY r.receipt_no, r.receive_date";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Receives info = new M_FIM_Receives();
                        info.receive_seq = Convert.ToInt32(drc[i]["receive_seq"]);
                        info.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info.work_area_seq = cn.ConvertNullInt(drc[i]["work_area_seq"]);
                        info.bgm_year = cn.ConvertNullInt(drc[i]["bgm_year"]);
                        info.receive_type_seq = cn.ConvertNullInt(drc[i]["receive_type_seq"]);
                        info.receipt_no = drc[i]["receipt_no"].ToString();
                        info.receive_date = cn.ConvertNullDateTime(drc[i]["receive_date"]);
                        info.manual_receipt = drc[i]["manual_receipt"].ToString();
                        info.receive_mode = drc[i]["receive_mode"].ToString();
                        info.method_seq = cn.ConvertNullInt(drc[i]["method_seq"]);
                        info.cheque_no = drc[i]["cheque_no"].ToString();
                        info.branch_seq = cn.ConvertNullInt(drc[i]["branch_seq"]);
                        info.bank_acc_seq = cn.ConvertNullInt(drc[i]["bank_acc_seq"]);
                        info.contact_seq = cn.ConvertNullInt(drc[i]["contact_seq"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.temp_contact = drc[i]["temp_contact"].ToString();
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.received_by = cn.ConvertNullInt(drc[i]["received_by"]);
                        info.remarks = drc[i]["remarks"].ToString();
                        info.tran_grp_seq = cn.ConvertNullInt(drc[i]["tran_grp_seq"]);
                        info.deposit_seq = cn.ConvertNullInt(drc[i]["deposit_seq"]);
                        info.petition_seq = cn.ConvertNullInt(drc[i]["petition_seq"]);
                        info.money_req_seq = cn.ConvertNullInt(drc[i]["money_req_seq"]);
                        info.pay_seq = cn.ConvertNullInt(drc[i]["pay_seq"]);
                        info.payer_name = drc[i]["payer_name"].ToString();
                        info.icm_bid_rec_seq = cn.ConvertNullInt(drc[i]["icm_bid_rec_seq"]);
                        info.icm_bid_off_col_seq = cn.ConvertNullInt(drc[i]["icm_bid_off_col_seq"]);
                        info.icm_po_con_col_seq = cn.ConvertNullInt(drc[i]["icm_po_con_col_seq"]);
                        info.icm_po_pre_col_seq = cn.ConvertNullInt(drc[i]["icm_po_pre_col_seq"]);
                        info.icm_dn_pen_seq = cn.ConvertNullInt(drc[i]["icm_dn_pen_seq"]);
                        info.icm_po_instal_seq = cn.ConvertNullInt(drc[i]["icm_po_instal_seq"]);
                        info.icm_stockmm_seq = cn.ConvertNullInt(drc[i]["icm_stockmm_seq"]);
                        info.receive_type_name = drc[i]["receive_type_name"].ToString();
                        info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        info.work_area_name = drc[i]["work_area_name"].ToString();
                        info.contact_name = drc[i]["NameTh"].ToString();
                        info.method_name = drc[i]["method_name"].ToString();
                        info.bank_seq = cn.ConvertNullInt(drc[i]["bank_seq"]);
                        info.bank_id = drc[i]["bank_id"].ToString();
                        info.bank_name = drc[i]["bank_name"].ToString();
                        info.branch_id = drc[i]["branch_id"].ToString();
                        info.branch_name = drc[i]["branch_name"].ToString();
                        info.bank_acc_no = drc[i]["bank_acc_no"].ToString();
                        info.bank_acc_name = drc[i]["bank_acc_name"].ToString();
                        info.emp_name = drc[i]["emp_name"].ToString();
                        info.received_by_name = drc[i]["received_by_name"].ToString();
                        info.event_seq = cn.ConvertNullInt(drc[i]["event_seq"]);

                        info.record_status = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);

                        info.vat_amount = cn.ConvertNullDecimal(drc[i]["vat_amount"]);
                        info.exclude_vat = cn.ConvertNullDecimal(drc[i]["exclude_vat"]);
                        info.vat_percent = cn.ConvertNullDecimal(drc[i]["vat_percent"]);
                        info.recive_remark = drc[i]["recive_remark"].ToString();
                        info.tax_percent = cn.ConvertNullDecimal(drc[i]["tax_percent"]);
                        info.tax = cn.ConvertNullDecimal(drc[i]["tax"]);
                        info.include_vat = cn.ConvertNullDecimal(drc[i]["include_vat"]);
                        info.ref_no = (drc[i]["ref_no"]).ToString();
                        info.contact_address = (drc[i]["contact_address"]).ToString();
                        info.contact_branch = (drc[i]["contact_branch"]).ToString(); 
                        info.contact_tax_code = (drc[i]["contact_tax_code"]).ToString();
                        info.temp_contact = (drc[i]["temp_contact"]).ToString(); 
                        info.source = (drc[i]["source"]).ToString();
                        info.print_out = Convert.ToInt32(drc[i]["print_out"]);

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_Receives> GetFull(Nullable<int> bgm_source_seq, Nullable<int> work_area_seq
            , Nullable<int> bgm_year, Nullable<int> receive_mode)
        {
            string strSQL;
            Nullable<int> tempReceiveMode = 0;

            DataRowCollection drc = null;
            IList<M_FIM_Receives> infoList = new List<M_FIM_Receives>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                if (receive_mode == 3)
                {
                    tempReceiveMode = 2;
                }
                else
                {
                    tempReceiveMode = receive_mode;
                }

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(3, "@bgm_year", bgm_year);
                dbManager.AddParameters(4, "@receive_mode", tempReceiveMode);

                strSQL = "SELECT r.receive_seq, r.bgm_source_seq, r.work_area_seq, r.bgm_year, r.vat_amount, r.exclude_vat, r.vat_percent, r.include_vat"
                    + ", r.receive_type_seq, r.receipt_no, r.ref_no, r.receive_date, r.manual_receipt, r.receive_mode, r.method_seq"
                    + ", r.cheque_no, r.branch_seq, r.bank_acc_seq, r.contact_seq"
                    + ", r.emp_seq, r.temp_contact, r.amount, r.recive_remark, r.tax_percent, r.tax"
                    + ", r.received_by, r.remarks, r.tran_grp_seq, r.deposit_seq"
                    + ", r.petition_seq, r.money_req_seq, r.pay_seq, r.record_status, r.source, r.print_out"
                    + ", r.contact_address, r.contact_branch, r.contact_tax_code, r.temp_contact"
                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end as payer_name"
                    + ", r.icm_bid_rec_seq, r.icm_bid_off_col_seq, r.icm_po_con_col_seq, r.icm_po_pre_col_seq"
                    + ", r.icm_dn_pen_seq, r.icm_po_instal_seq, r.icm_stockmm_seq"

                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', coalesce(mr.ref_no, ''))"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', coalesce(pt.ref_no, ''))"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"
                    + ", bb.bank_seq, b.bank_id, b.bank_name, bb.branch_id, bb.branch_name"
                    + ", acc.bank_acc_no, acc.bank_acc_name"

                    + ", s.bgm_source_name, area.work_area_name, c.NameTh"
                    + ", m.method_name"
                    + ", mr.event_seq"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", concat(recnp.prefix_name, rec.first_name, ' ', rec.last_name) as received_by_name"
                    + ", rs.record_status_name"
                    + ", r.created_by, r.created_when, r.updated_by, r.updated_when, r.qty, r.discount_percent, r.discount, r.unit_price"
                    + " FROM fim_receives r"                    
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_mas_methods m ON r.method_seq = m.method_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON r.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON r.bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN bgm_mas_source s ON r.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_ps_employees rec ON r.received_by = rec.emp_seq"
                    + " LEFT JOIN mas_name_prefixes recnp ON rec.name_prefix_seq = recnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON r.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN sys_record_status rs ON r.record_status = rs.record_status"
                    + " WHERE r.record_status not like 'D'"
                    + " AND r.receive_mode = @receive_mode"
                    + (bgm_source_seq != null ? " AND r.bgm_source_seq = @bgm_source_seq" : "")
                    + (work_area_seq != null ? " AND r.work_area_seq = @work_area_seq" : "")
                    + (bgm_year != null ? " AND r.bgm_year = @bgm_year" : "")
                    + (receive_mode != null ? "" : " AND r.receive_mode = @receive_mode")
                    //+ " AND r.print_out = 0"
                    + " ORDER BY r.receipt_no, r.receive_date";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Receives info = new M_FIM_Receives();
                        info.receive_seq = Convert.ToInt32(drc[i]["receive_seq"]);
                        info.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info.work_area_seq = cn.ConvertNullInt(drc[i]["work_area_seq"]);
                        info.bgm_year = cn.ConvertNullInt(drc[i]["bgm_year"]);
                        info.receive_type_seq = cn.ConvertNullInt(drc[i]["receive_type_seq"]);
                        info.receipt_no = drc[i]["receipt_no"].ToString();
                        info.receive_date = cn.ConvertNullDateTime(drc[i]["receive_date"]);
                        info.manual_receipt = drc[i]["manual_receipt"].ToString();
                        info.receive_mode = drc[i]["receive_mode"].ToString();
                        info.method_seq = cn.ConvertNullInt(drc[i]["method_seq"]);
                        info.cheque_no = drc[i]["cheque_no"].ToString();
                        info.branch_seq = cn.ConvertNullInt(drc[i]["branch_seq"]);
                        info.bank_acc_seq = cn.ConvertNullInt(drc[i]["bank_acc_seq"]);
                        info.contact_seq = cn.ConvertNullInt(drc[i]["contact_seq"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.temp_contact = drc[i]["temp_contact"].ToString();
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.received_by = cn.ConvertNullInt(drc[i]["received_by"]);
                        info.remarks = drc[i]["remarks"].ToString();
                        info.tran_grp_seq = cn.ConvertNullInt(drc[i]["tran_grp_seq"]);
                        info.deposit_seq = cn.ConvertNullInt(drc[i]["deposit_seq"]);
                        info.petition_seq = cn.ConvertNullInt(drc[i]["petition_seq"]);
                        info.money_req_seq = cn.ConvertNullInt(drc[i]["money_req_seq"]);
                        info.pay_seq = cn.ConvertNullInt(drc[i]["pay_seq"]);
                        info.payer_name = drc[i]["payer_name"].ToString();
                        info.icm_bid_rec_seq = cn.ConvertNullInt(drc[i]["icm_bid_rec_seq"]);
                        info.icm_bid_off_col_seq = cn.ConvertNullInt(drc[i]["icm_bid_off_col_seq"]);
                        info.icm_po_con_col_seq = cn.ConvertNullInt(drc[i]["icm_po_con_col_seq"]);
                        info.icm_po_pre_col_seq = cn.ConvertNullInt(drc[i]["icm_po_pre_col_seq"]);
                        info.icm_dn_pen_seq = cn.ConvertNullInt(drc[i]["icm_dn_pen_seq"]);
                        info.icm_po_instal_seq = cn.ConvertNullInt(drc[i]["icm_po_instal_seq"]);
                        info.icm_stockmm_seq = cn.ConvertNullInt(drc[i]["icm_stockmm_seq"]);
                        info.receive_type_name = drc[i]["receive_type_name"].ToString();
                        info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        info.work_area_name = drc[i]["work_area_name"].ToString();
                        info.contact_name = drc[i]["NameTh"].ToString();
                        info.method_name = drc[i]["method_name"].ToString();
                        info.bank_seq = cn.ConvertNullInt(drc[i]["bank_seq"]);
                        info.bank_id = drc[i]["bank_id"].ToString();
                        info.bank_name = drc[i]["bank_name"].ToString();
                        info.branch_id = drc[i]["branch_id"].ToString();
                        info.branch_name = drc[i]["branch_name"].ToString();
                        info.bank_acc_no = drc[i]["bank_acc_no"].ToString();
                        info.bank_acc_name = drc[i]["bank_acc_name"].ToString();
                        info.emp_name = drc[i]["emp_name"].ToString();
                        info.received_by_name = drc[i]["received_by_name"].ToString();
                        info.event_seq = cn.ConvertNullInt(drc[i]["event_seq"]);

                        info.record_status = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);

                        info.vat_amount = cn.ConvertNullDecimal(drc[i]["vat_amount"]);
                        info.exclude_vat = cn.ConvertNullDecimal(drc[i]["exclude_vat"]);
                        info.vat_percent = cn.ConvertNullDecimal(drc[i]["vat_percent"]);
                        info.recive_remark = drc[i]["recive_remark"].ToString();
                        info.tax_percent = cn.ConvertNullDecimal(drc[i]["tax_percent"]);
                        info.tax = cn.ConvertNullDecimal(drc[i]["tax"]);
                        info.include_vat = cn.ConvertNullDecimal(drc[i]["include_vat"]);
                        info.ref_no = (drc[i]["ref_no"]).ToString();
                        info.contact_address = (drc[i]["contact_address"]).ToString();
                        info.contact_branch = (drc[i]["contact_branch"]).ToString();
                        info.contact_tax_code = (drc[i]["contact_tax_code"]).ToString();
                        info.temp_contact = (drc[i]["temp_contact"]).ToString();
                        info.source = (drc[i]["source"]).ToString();
                        info.print_out = Convert.ToInt32(drc[i]["print_out"]);
                        info.qty = cn.ConvertNullInt(drc[i]["qty"]);
                        info.discount_percent = cn.ConvertNullDecimal(drc[i]["discount_percent"]);
                        info.discount = cn.ConvertNullDecimal(drc[i]["discount"]);
                        info.unit_price = cn.ConvertNullDecimal(drc[i]["unit_price"]);

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public DataTable GetFullPrepare(string bgm_source_seq, string work_area_seq, string bgm_year, string source, string receive_seq)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(3, "@bgm_year", bgm_year);
                dbManager.AddParameters(4, "@source", source);
                dbManager.AddParameters(5, "@receive_seq", receive_seq);

                strSQL = "SELECT r.receive_seq, r.bgm_source_seq, r.work_area_seq, r.bgm_year, r.vat_amount, r.exclude_vat, r.vat_percent, r.include_vat"
                    + ", r.receive_type_seq, r.receipt_no, r.ref_no, r.receive_date, r.manual_receipt, r.receive_mode, r.method_seq"
                    + ", r.cheque_no, r.branch_seq, r.bank_acc_seq, r.contact_seq"
                    + ", r.emp_seq, r.temp_contact, r.amount, r.recive_remark, r.tax_percent, r.tax"
                    + ", r.received_by, r.remarks, r.tran_grp_seq, r.deposit_seq"
                    + ", r.petition_seq, r.money_req_seq, r.pay_seq, r.record_status, r.source, r.print_out"
                    + ", r.contact_address, r.contact_branch, r.contact_tax_code, r.temp_contact"
                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end as payer_name"
                    + ", r.icm_bid_rec_seq, r.icm_bid_off_col_seq, r.icm_po_con_col_seq, r.icm_po_pre_col_seq"
                    + ", r.icm_dn_pen_seq, r.icm_po_instal_seq, r.icm_stockmm_seq"

                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', coalesce(mr.ref_no, ''))"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', coalesce(pt.ref_no, ''))"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"
                    + ", bb.bank_seq, b.bank_id, b.bank_name, bb.branch_id, bb.branch_name"
                    + ", acc.bank_acc_no, acc.bank_acc_name"

                    + ", s.bgm_source_name, area.work_area_name, c.NameTh"
                    + ", m.method_name"
                    + ", mr.event_seq"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", concat(recnp.prefix_name, rec.first_name, ' ', rec.last_name) as received_by_name"
                    + ", rs.record_status_name"
                    + ", r.created_by, r.created_when, r.updated_by, r.updated_when"
                    + " FROM fim_receives r"
                    //+ " LEFT JOIN (select receive_seq"
                    //    + ", sum(coalesce(bath_amount, 0)) as total_bath_amount"
                    //    + " from fim_receive_dtls"
                    //    + " where record_status not like 'D'"
                    //    + " group by receive_seq"
                    //    + ") rd on r.receive_seq = rd.receive_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_mas_methods m ON r.method_seq = m.method_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON r.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON r.bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN bgm_mas_source s ON r.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_ps_employees rec ON r.received_by = rec.emp_seq"
                    + " LEFT JOIN mas_name_prefixes recnp ON rec.name_prefix_seq = recnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON r.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN sys_record_status rs ON r.record_status = rs.record_status"
                    + " WHERE r.record_status not like 'D'"
                    + (bgm_source_seq != null ? " AND r.bgm_source_seq = @bgm_source_seq" : "")
                    + (work_area_seq != null ? " AND r.work_area_seq = @work_area_seq" : "")
                    + (source != null ? " AND r.source = @source" : "")
                    + (bgm_year != null ? " AND r.bgm_year = @bgm_year" : "")
                    + (receive_seq != null ? " AND r.receive_seq = @receive_seq " : "")
                    + " AND r.receive_mode not like '3'  AND r.print_out = 0 "
                    + " ORDER BY r.receipt_no, r.receive_date";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
             
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }

        public IList<M_FIM_Receives> GetNotDeposits(Nullable<int> bgm_source_seq, Nullable<int> work_area_seq
            , Nullable<int> bgm_year, Nullable<int> transfer_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Receives> infoList = new List<M_FIM_Receives>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(3, "@bgm_year", bgm_year);
                dbManager.AddParameters(4, "@transfer_seq", transfer_seq);

                strSQL = "SELECT r.receive_seq, r.bgm_source_seq, r.work_area_seq, r.bgm_year"
                    + ", r.receive_type_seq, r.receipt_no, r.ref_no, r.recive_remark, r.exclude_vat, r.vat_percent, r.vat_amount, r.include_vat,  r.tax_percent, r.tax"
                    + ", r.receive_date, r.manual_receipt, r.receive_mode, r.method_seq"
                    + ", r.cheque_no, r.branch_seq, r.bank_acc_seq, r.contact_seq"
                    + ", r.emp_seq, r.temp_contact, r.amount"
                    + ", r.received_by, r.remarks, r.tran_grp_seq, r.deposit_seq"
                    + ", r.petition_seq, r.money_req_seq, r.pay_seq, r.record_status"
                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end as payer_name"
                    + ", r.icm_bid_rec_seq, r.icm_bid_off_col_seq, r.icm_po_con_col_seq, r.icm_po_pre_col_seq"
                    + ", r.icm_dn_pen_seq, r.icm_po_instal_seq, r.icm_stockmm_seq"

                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"
                    + ", bb.bank_seq, b.bank_id, b.bank_name, bb.branch_id, bb.branch_name"
                    + ", acc.bank_acc_no, acc.bank_acc_name"

                    + ", s.bgm_source_name, area.work_area_name, c.NameTh"
                    + ", m.method_name"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", concat(recnp.prefix_name, rec.first_name, ' ', rec.last_name) as received_by_name"
                    + ", rs.record_status_name"
                    + ", r.created_by, r.created_when, r.updated_by, r.updated_when"
                    + " FROM fim_receives r"
                    //+ " LEFT JOIN (select receive_seq"
                    //    //+ ", sum(coalesce(amount, 0)) as total_amount"
                    //    //+ ", currency_seq, exchange_rate"
                    //    + ", sum(coalesce(bath_amount, 0)) as total_bath_amount"
                    //    + " from fim_receive_dtls"
                    //    + " where record_status not like 'D'"
                    //    + " group by receive_seq"//, currency_seq, exchange_rate"
                    //    + ") rd on r.receive_seq = rd.receive_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_mas_methods m ON r.method_seq = m.method_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON r.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON r.bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN bgm_mas_source s ON r.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
                    //+ " LEFT JOIN fim_mas_contact_persons cp ON r.contact_person_seq = cp.contact_person_seq"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_ps_employees rec ON r.received_by = rec.emp_seq"
                    + " LEFT JOIN mas_name_prefixes recnp ON rec.name_prefix_seq = recnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON r.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN sys_record_status rs ON r.record_status = rs.record_status"
                    + " WHERE r.record_status not in ('D', 'C')"
                    + " AND (r.method_seq = 1 OR r.method_seq = 2)"
                    + (bgm_source_seq != null ? " AND r.bgm_source_seq = @bgm_source_seq" : "")
                    + (work_area_seq != null ? " AND r.work_area_seq = @work_area_seq" : "")
                    + (bgm_year != null ? " AND r.bgm_year = @bgm_year" : "")
                    + (transfer_seq != null ?
                        " AND ((r.deposit_seq is null AND r.pay_seq is null) OR r.deposit_seq = @transfer_seq)"
                        : " AND (r.deposit_seq is null) AND r.pay_seq is null")
                    + " ORDER BY r.receipt_no, r.receive_date";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Receives info = new M_FIM_Receives();
                        info.receive_seq = Convert.ToInt32(drc[i]["receive_seq"]);
                        info.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info.work_area_seq = cn.ConvertNullInt(drc[i]["work_area_seq"]);
                        info.bgm_year = cn.ConvertNullInt(drc[i]["bgm_year"]);
                        info.receive_type_seq = cn.ConvertNullInt(drc[i]["receive_type_seq"]);
                        info.receipt_no = drc[i]["receipt_no"].ToString();
                        info.receive_date = cn.ConvertNullDateTime(drc[i]["receive_date"]);
                        info.manual_receipt = drc[i]["manual_receipt"].ToString();
                        info.receive_mode = drc[i]["receive_mode"].ToString();
                        info.method_seq = cn.ConvertNullInt(drc[i]["method_seq"]);
                        info.cheque_no = drc[i]["cheque_no"].ToString();
                        info.branch_seq = cn.ConvertNullInt(drc[i]["branch_seq"]);
                        info.bank_acc_seq = cn.ConvertNullInt(drc[i]["bank_acc_seq"]);
                        info.contact_seq = cn.ConvertNullInt(drc[i]["contact_seq"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.temp_contact = drc[i]["temp_contact"].ToString();
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.received_by = cn.ConvertNullInt(drc[i]["received_by"]);
                        info.remarks = drc[i]["remarks"].ToString();
                        info.tran_grp_seq = cn.ConvertNullInt(drc[i]["tran_grp_seq"]);
                        info.deposit_seq = cn.ConvertNullInt(drc[i]["deposit_seq"]);
                        info.petition_seq = cn.ConvertNullInt(drc[i]["petition_seq"]);
                        info.money_req_seq = cn.ConvertNullInt(drc[i]["money_req_seq"]);
                        info.pay_seq = cn.ConvertNullInt(drc[i]["pay_seq"]);
                        info.payer_name = drc[i]["payer_name"].ToString();
                        info.icm_bid_rec_seq = cn.ConvertNullInt(drc[i]["icm_bid_rec_seq"]);
                        info.icm_bid_off_col_seq = cn.ConvertNullInt(drc[i]["icm_bid_off_col_seq"]);
                        info.icm_po_con_col_seq = cn.ConvertNullInt(drc[i]["icm_po_con_col_seq"]);
                        info.icm_po_pre_col_seq = cn.ConvertNullInt(drc[i]["icm_po_pre_col_seq"]);
                        info.icm_dn_pen_seq = cn.ConvertNullInt(drc[i]["icm_dn_pen_seq"]);
                        info.icm_po_instal_seq = cn.ConvertNullInt(drc[i]["icm_po_instal_seq"]);
                        info.icm_stockmm_seq = cn.ConvertNullInt(drc[i]["icm_stockmm_seq"]);
                        info.receive_type_name = drc[i]["receive_type_name"].ToString();
                        info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        info.work_area_name = drc[i]["work_area_name"].ToString();
                        info.contact_name = drc[i]["NameTh"].ToString();
                        info.method_name = drc[i]["method_name"].ToString();
                        info.bank_seq = cn.ConvertNullInt(drc[i]["bank_seq"]);
                        info.bank_id = drc[i]["bank_id"].ToString();
                        info.bank_name = drc[i]["bank_name"].ToString();
                        info.branch_id = drc[i]["branch_id"].ToString();
                        info.branch_name = drc[i]["branch_name"].ToString();
                        info.bank_acc_no = drc[i]["bank_acc_no"].ToString();
                        info.bank_acc_name = drc[i]["bank_acc_name"].ToString();
                        info.emp_name = drc[i]["emp_name"].ToString();
                        info.received_by_name = drc[i]["received_by_name"].ToString();
                        info.recive_remark = drc[i]["recive_remark"].ToString();
                        info.vat_amount = cn.ConvertNullDecimal(drc[i]["vat_amount"]);
                        info.exclude_vat = cn.ConvertNullDecimal(drc[i]["exclude_vat"]);
                        info.vat_percent = cn.ConvertNullDecimal(drc[i]["vat_percent"]);
                        info.tax_percent = cn.ConvertNullDecimal(drc[i]["tax_percent"]);
                        info.tax = cn.ConvertNullDecimal(drc[i]["tax"]);
                        info.include_vat = cn.ConvertNullDecimal(drc[i]["include_vat"]);
                        info.ref_no = (drc[0]["ref_no"]).ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_Receives> GetPayments(int pay_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Receives> infoList = new List<M_FIM_Receives>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@pay_seq", pay_seq);

                strSQL = "SELECT r.receive_seq, r.bgm_source_seq, r.work_area_seq, r.bgm_year"
                    + ", r.receive_type_seq, r.receipt_no, r.ref_no, r.recive_remark, r.exclude_vat, r.vat_percent, r.vat_amount, r.include_vat,  r.tax_percent, r.tax"
                    + ", r.receive_date, r.manual_receipt, r.receive_mode, r.method_seq"
                    + ", r.cheque_no, r.branch_seq, r.bank_acc_seq, r.contact_seq"
                    + ", r.emp_seq, r.temp_contact, r.amount"
                    + ", r.received_by, r.remarks, r.tran_grp_seq, r.deposit_seq"
                    + ", r.petition_seq, r.money_req_seq, r.pay_seq, r.record_status"
                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end as payer_name"
                    + ", r.icm_bid_rec_seq, r.icm_bid_off_col_seq, r.icm_po_con_col_seq, r.icm_po_pre_col_seq"
                    + ", r.icm_dn_pen_seq, r.icm_po_instal_seq, r.icm_stockmm_seq"

                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"
                    + ", bb.bank_seq, b.bank_id, b.bank_name, bb.branch_id, bb.branch_name"
                    + ", acc.bank_acc_no, acc.bank_acc_name"

                    + ", s.bgm_source_name, area.work_area_name, c.NameTh"
                    + ", m.method_name"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", concat(recnp.prefix_name, rec.first_name, ' ', rec.last_name) as received_by_name"
                    + ", rs.record_status_name"
                    + ", r.created_by, r.created_when, r.updated_by, r.updated_when"
                    + " FROM fim_receives r"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_mas_methods m ON r.method_seq = m.method_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON r.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON r.bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN bgm_mas_source s ON r.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_ps_employees rec ON r.received_by = rec.emp_seq"
                    + " LEFT JOIN mas_name_prefixes recnp ON rec.name_prefix_seq = recnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON r.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN sys_record_status rs ON r.record_status = rs.record_status"
                    + " WHERE r.record_status not in ('D', 'C')"
                    + " and r.pay_seq = @pay_seq"
                    + " ORDER BY r.receipt_no, r.receive_date";


                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Receives info = new M_FIM_Receives();
                        info.receive_seq = Convert.ToInt32(drc[i]["receive_seq"]);
                        info.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info.work_area_seq = cn.ConvertNullInt(drc[i]["work_area_seq"]);
                        info.bgm_year = cn.ConvertNullInt(drc[i]["bgm_year"]);
                        info.receive_type_seq = cn.ConvertNullInt(drc[i]["receive_type_seq"]);
                        info.receipt_no = drc[i]["receipt_no"].ToString();
                        info.receive_date = cn.ConvertNullDateTime(drc[i]["receive_date"]);
                        info.manual_receipt = drc[i]["manual_receipt"].ToString();
                        info.receive_mode = drc[i]["receive_mode"].ToString();
                        info.method_seq = cn.ConvertNullInt(drc[i]["method_seq"]);
                        info.cheque_no = drc[i]["cheque_no"].ToString();
                        info.branch_seq = cn.ConvertNullInt(drc[i]["branch_seq"]);
                        info.bank_acc_seq = cn.ConvertNullInt(drc[i]["bank_acc_seq"]);
                        info.contact_seq = cn.ConvertNullInt(drc[i]["contact_seq"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.temp_contact = drc[i]["temp_contact"].ToString();
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.received_by = cn.ConvertNullInt(drc[i]["received_by"]);
                        info.remarks = drc[i]["remarks"].ToString();
                        info.tran_grp_seq = cn.ConvertNullInt(drc[i]["tran_grp_seq"]);
                        info.deposit_seq = cn.ConvertNullInt(drc[i]["deposit_seq"]);
                        info.petition_seq = cn.ConvertNullInt(drc[i]["petition_seq"]);
                        info.money_req_seq = cn.ConvertNullInt(drc[i]["money_req_seq"]);
                        info.pay_seq = cn.ConvertNullInt(drc[i]["pay_seq"]);
                        info.payer_name = drc[i]["payer_name"].ToString();
                        info.icm_bid_rec_seq = cn.ConvertNullInt(drc[i]["icm_bid_rec_seq"]);
                        info.icm_bid_off_col_seq = cn.ConvertNullInt(drc[i]["icm_bid_off_col_seq"]);
                        info.icm_po_con_col_seq = cn.ConvertNullInt(drc[i]["icm_po_con_col_seq"]);
                        info.icm_po_pre_col_seq = cn.ConvertNullInt(drc[i]["icm_po_pre_col_seq"]);
                        info.icm_dn_pen_seq = cn.ConvertNullInt(drc[i]["icm_dn_pen_seq"]);
                        info.icm_po_instal_seq = cn.ConvertNullInt(drc[i]["icm_po_instal_seq"]);
                        info.icm_stockmm_seq = cn.ConvertNullInt(drc[i]["icm_stockmm_seq"]);
                        info.receive_type_name = drc[i]["receive_type_name"].ToString();
                        info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        info.work_area_name = drc[i]["work_area_name"].ToString();
                        info.contact_name = drc[i]["NameTh"].ToString();
                        info.method_name = drc[i]["method_name"].ToString();
                        info.bank_seq = cn.ConvertNullInt(drc[i]["bank_seq"]);
                        info.bank_id = drc[i]["bank_id"].ToString();
                        info.bank_name = drc[i]["bank_name"].ToString();
                        info.branch_id = drc[i]["branch_id"].ToString();
                        info.branch_name = drc[i]["branch_name"].ToString();
                        info.bank_acc_no = drc[i]["bank_acc_no"].ToString();
                        info.bank_acc_name = drc[i]["bank_acc_name"].ToString();
                        info.emp_name = drc[i]["emp_name"].ToString();
                        info.received_by_name = drc[i]["received_by_name"].ToString();
                        info.recive_remark = drc[i]["recive_remark"].ToString();
                        info.vat_amount = cn.ConvertNullDecimal(drc[i]["vat_amount"]);
                        info.exclude_vat = cn.ConvertNullDecimal(drc[i]["exclude_vat"]);
                        info.vat_percent = cn.ConvertNullDecimal(drc[i]["vat_percent"]);
                        info.tax_percent = cn.ConvertNullDecimal(drc[i]["tax_percent"]);
                        info.tax = cn.ConvertNullDecimal(drc[i]["tax"]);
                        info.include_vat = cn.ConvertNullDecimal(drc[i]["include_vat"]);
                        info.ref_no = (drc[0]["ref_no"]).ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_Receives> GetNotPayments(Nullable<int> bgm_source_seq, Nullable<int> work_area_seq
            , Nullable<int> bgm_year, Nullable<int> pay_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Receives> infoList = new List<M_FIM_Receives>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(3, "@bgm_year", bgm_year);
                dbManager.AddParameters(4, "@pay_seq", pay_seq);

                strSQL = "SELECT r.receive_seq, concat(coalesce(r.receipt_no,''), ' - ', case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end) as receipt_no, r.amount"
                    + " FROM fim_receives r"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " WHERE r.record_status not in ('D', 'C')"
                    + (bgm_source_seq != null ? " AND r.bgm_source_seq = @bgm_source_seq" : "")
                    + (work_area_seq != null ? " AND r.work_area_seq = @work_area_seq" : "")
                    + (bgm_year != null ? " AND r.bgm_year = @bgm_year" : "")
                    + (pay_seq != null ? " AND (r.pay_seq is null or r.pay_seq = @pay_seq)" : " AND r.pay_seq is null")
                    + " and r.receive_mode = 2"
                    + " ORDER BY r.receipt_no, r.receive_date";

                //strSQL = "SELECT r.receive_seq, r.bgm_source_seq, r.work_area_seq, r.bgm_year"
                //    + ", r.receive_type_seq, r.receipt_no, r.ref_no, r.recive_remark, r.exclude_vat, r.vat_percent, r.vat_amount, r.include_vat,  r.tax_percent, r.tax"
                //    + ", r.receive_date, r.manual_receipt, r.receive_mode, r.method_seq"
                //    + ", r.cheque_no, r.branch_seq, r.bank_acc_seq, r.contact_seq"
                //    + ", r.emp_seq, r.temp_contact, r.amount"
                //    + ", r.received_by, r.remarks, r.tran_grp_seq, r.deposit_seq"
                //    + ", r.petition_seq, r.money_req_seq, r.pay_seq, r.record_status"
                //    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                //        + " else case when r.contact_seq is not null then c.NameTh"
                //            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                //            + " end"
                //        + " end as payer_name"
                //    + ", r.icm_bid_rec_seq, r.icm_bid_off_col_seq, r.icm_po_con_col_seq, r.icm_po_pre_col_seq"
                //    + ", r.icm_dn_pen_seq, r.icm_po_instal_seq, r.icm_stockmm_seq"

                //    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                //        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                //            + " ELSE rt.receive_type_name"
                //            + " END"
                //        + " END AS receive_type_name"
                //    + ", bb.bank_seq, b.bank_id, b.bank_name, bb.branch_id, bb.branch_name"
                //    + ", acc.bank_acc_no, acc.bank_acc_name"

                //    + ", s.bgm_source_name, area.work_area_name, c.NameTh"
                //    + ", m.method_name"
                //    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                //    + ", concat(recnp.prefix_name, rec.first_name, ' ', rec.last_name) as received_by_name"
                //    + ", rs.record_status_name"
                //    + ", r.created_by, r.created_when, r.updated_by, r.updated_when"
                //    + " FROM fim_receives r"
                //    //+ " LEFT JOIN (select receive_seq"
                //    //    + ", sum(coalesce(bath_amount, 0)) as total_bath_amount"
                //    //    + " from fim_receive_dtls"
                //    //    + " where record_status not like 'D'"
                //    //    + " group by receive_seq"
                //    //    + ") rd on r.receive_seq = rd.receive_seq"
                //    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                //    + " LEFT JOIN fim_mas_methods m ON r.method_seq = m.method_seq"
                //    + " LEFT JOIN fim_mas_bank_branchs bb ON r.branch_seq = bb.branch_seq"
                //    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                //    + " LEFT JOIN fim_mas_bank_accounts acc ON r.bank_acc_seq = acc.bank_acc_seq"
                //    + " LEFT JOIN bgm_mas_source s ON r.bgm_source_seq = s.bgm_source_seq"
                //    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
                //    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                //    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                //    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                //    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                //    + " LEFT JOIN hrm_ps_employees rec ON r.received_by = rec.emp_seq"
                //    + " LEFT JOIN mas_name_prefixes recnp ON rec.name_prefix_seq = recnp.name_prefix_seq"
                //    + " LEFT JOIN hrm_mp_work_areas area ON r.work_area_seq = area.work_area_seq"
                //    + " LEFT JOIN sys_record_status rs ON r.record_status = rs.record_status"
                //    + " WHERE r.record_status not like 'D'"
                //    + (bgm_source_seq != null ? " AND r.bgm_source_seq = @bgm_source_seq" : "")
                //    + (work_area_seq != null ? " AND r.work_area_seq = @work_area_seq" : "")
                //    + (bgm_year != null ? " AND r.bgm_year = @bgm_year" : "")
                //    + " AND r.pay_seq is null"
                //    + " and r.receive_mode = 2"
                //    + " ORDER BY r.receipt_no, r.receive_date";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Receives info = new M_FIM_Receives();
                        info.receive_seq = Convert.ToInt32(drc[i]["receive_seq"]);
                        info.receipt_no = drc[i]["receipt_no"].ToString() + " - " + (cn.ConvertNullDouble(drc[i]["amount"]) ?? 0).ToString("#,##0.00");
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_Receives> GetNotPaymentBacks(Nullable<int> bgm_source_seq, Nullable<int> work_area_seq
            , Nullable<int> bgm_year, Nullable<int> pay_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Receives> infoList = new List<M_FIM_Receives>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(3, "@bgm_year", bgm_year);
                dbManager.AddParameters(4, "@pay_seq", pay_seq);

                strSQL = "SELECT r.receive_seq, r.bgm_source_seq, r.work_area_seq, r.bgm_year"
                    + ", r.receive_type_seq, r.receipt_no, r.ref_no,  r.recive_remark, r.exclude_vat, r.vat_amount, r.vat_percent, r.include_vat,  r.tax_percent, r.tax"
                    + ", r.receive_date, r.manual_receipt, r.receive_mode, r.method_seq"
                    + ", r.cheque_no, r.branch_seq, r.bank_acc_seq, r.contact_seq"
                    + ", r.emp_seq, r.temp_contact, r.amount"//, r.currency_seq, r.exchange_rate, r.bath_amount"
                    + ", r.received_by, r.remarks, r.tran_grp_seq, r.deposit_seq"
                    + ", r.petition_seq, r.money_req_seq, r.pay_seq, r.record_status"
                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end as payer_name"
                    + ", r.icm_bid_rec_seq, r.icm_bid_off_col_seq, r.icm_po_con_col_seq, r.icm_po_pre_col_seq"
                    + ", r.icm_dn_pen_seq, r.icm_po_instal_seq, r.icm_stockmm_seq"

                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"
                    + ", bb.bank_seq, b.bank_id, b.bank_name, bb.branch_id, bb.branch_name"
                    + ", acc.bank_acc_no, acc.bank_acc_name"

                    + ", s.bgm_source_name, area.work_area_name, c.NameTh"//, cp.contact_person_name"
                    + ", m.method_name"//, cur.currency_name"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", concat(recnp.prefix_name, rec.first_name, ' ', rec.last_name) as received_by_name"
                    + ", rs.record_status_name"
                    + ", r.created_by, r.created_when, r.updated_by, r.updated_when"
                    + " FROM fim_receives r"
                    //+ " LEFT JOIN (select receive_seq"
                    //    + ", sum(coalesce(bath_amount, 0)) as total_bath_amount"
                    //    + " from fim_receive_dtls"
                    //    + " where record_status not like 'D'"
                    //    + " group by receive_seq"
                    //    + ") rd on r.receive_seq = rd.receive_seq"
                    //+ " LEFT JOIN fim_mas_currencies cur ON r.currency_seq = cur.currency_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_mas_methods m ON r.method_seq = m.method_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON r.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON r.bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN bgm_mas_source s ON r.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
                    //+ " LEFT JOIN fim_mas_contact_persons cp ON r.contact_person_seq = cp.contact_person_seq"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_ps_employees rec ON r.received_by = rec.emp_seq"
                    + " LEFT JOIN mas_name_prefixes recnp ON rec.name_prefix_seq = recnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON r.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN sys_record_status rs ON r.record_status = rs.record_status"
                    + " WHERE r.record_status not in ('D', 'C')"
                    + " AND r.money_req_seq is not null"
                    + (bgm_source_seq != null ? " AND r.bgm_source_seq = @bgm_source_seq" : "")
                    + (work_area_seq != null ? " AND r.work_area_seq = @work_area_seq" : "")
                    + (bgm_year != null ? " AND r.bgm_year = @bgm_year" : "")
                    + (pay_seq != null ? " AND (r.pay_seq is null OR r.pay_seq = @pay_seq)"
                        : " AND (r.pay_seq is null)")
                    + " ORDER BY r.receipt_no, r.receive_date";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Receives info = new M_FIM_Receives();
                        info.receive_seq = Convert.ToInt32(drc[i]["receive_seq"]);
                        info.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info.work_area_seq = cn.ConvertNullInt(drc[i]["work_area_seq"]);
                        info.bgm_year = cn.ConvertNullInt(drc[i]["bgm_year"]);
                        info.receive_type_seq = cn.ConvertNullInt(drc[i]["receive_type_seq"]);
                        info.receipt_no = drc[i]["receipt_no"].ToString();
                        info.receive_date = cn.ConvertNullDateTime(drc[i]["receive_date"]);
                        info.manual_receipt = drc[i]["manual_receipt"].ToString();
                        info.receive_mode = drc[i]["receive_mode"].ToString();
                        info.method_seq = cn.ConvertNullInt(drc[i]["method_seq"]);
                        info.cheque_no = drc[i]["cheque_no"].ToString();
                        info.branch_seq = cn.ConvertNullInt(drc[i]["branch_seq"]);
                        info.bank_acc_seq = cn.ConvertNullInt(drc[i]["bank_acc_seq"]);
                        info.contact_seq = cn.ConvertNullInt(drc[i]["contact_seq"]);
                        //info.contact_person_seq = cn.ConvertNullInt(drc[i]["contact_person_seq"]);
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.temp_contact = drc[i]["temp_contact"].ToString();
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.received_by = cn.ConvertNullInt(drc[i]["received_by"]);
                        info.remarks = drc[i]["remarks"].ToString();
                        info.tran_grp_seq = cn.ConvertNullInt(drc[i]["tran_grp_seq"]);
                        info.deposit_seq = cn.ConvertNullInt(drc[i]["deposit_seq"]);
                        info.petition_seq = cn.ConvertNullInt(drc[i]["petition_seq"]);
                        info.money_req_seq = cn.ConvertNullInt(drc[i]["money_req_seq"]);
                        info.pay_seq = cn.ConvertNullInt(drc[i]["pay_seq"]);
                        info.payer_name = drc[i]["payer_name"].ToString();
                        info.icm_bid_rec_seq = cn.ConvertNullInt(drc[i]["icm_bid_rec_seq"]);
                        info.icm_bid_off_col_seq = cn.ConvertNullInt(drc[i]["icm_bid_off_col_seq"]);
                        info.icm_po_con_col_seq = cn.ConvertNullInt(drc[i]["icm_po_con_col_seq"]);
                        info.icm_po_pre_col_seq = cn.ConvertNullInt(drc[i]["icm_po_pre_col_seq"]);
                        info.icm_dn_pen_seq = cn.ConvertNullInt(drc[i]["icm_dn_pen_seq"]);
                        info.icm_po_instal_seq = cn.ConvertNullInt(drc[i]["icm_po_instal_seq"]);
                        info.icm_stockmm_seq = cn.ConvertNullInt(drc[i]["icm_stockmm_seq"]);
                        info.receive_type_name = drc[i]["receive_type_name"].ToString();
                        info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        info.work_area_name = drc[i]["work_area_name"].ToString();
                        info.contact_name = drc[i]["NameTh"].ToString();
                       // info.contact_person_name = drc[i]["contact_person_name"].ToString();
                        info.method_name = drc[i]["method_name"].ToString();
                        //info.currency_name = drc[i]["currency_name"].ToString();
                        info.bank_seq = cn.ConvertNullInt(drc[i]["bank_seq"]);
                        info.bank_id = drc[i]["bank_id"].ToString();
                        info.bank_name = drc[i]["bank_name"].ToString();
                        info.branch_id = drc[i]["branch_id"].ToString();
                        info.branch_name = drc[i]["branch_name"].ToString();
                        info.bank_acc_no = drc[i]["bank_acc_no"].ToString();
                        info.bank_acc_name = drc[i]["bank_acc_name"].ToString();
                        info.emp_name = drc[i]["emp_name"].ToString();
                        info.received_by_name = drc[i]["received_by_name"].ToString();
                        info.recive_remark = drc[i]["recive_remark"].ToString();
                        info.vat_amount = cn.ConvertNullDecimal(drc[i]["vat_amount"]);
                        info.exclude_vat = cn.ConvertNullDecimal(drc[i]["exclude_vat"]);
                        info.vat_percent = cn.ConvertNullDecimal(drc[i]["vat_percent"]);
                        info.tax_percent = cn.ConvertNullDecimal(drc[i]["tax_percent"]);
                        info.tax = cn.ConvertNullDecimal(drc[i]["tax"]);
                        info.include_vat = cn.ConvertNullDecimal(drc[i]["include_vat"]);
                        info.ref_no = (drc[0]["ref_no"]).ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_Receives> GetList(Nullable<int> bgm_source_seq, Nullable<int> work_area_seq
            , Nullable<int> bgm_year)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Receives> infoList = new List<M_FIM_Receives>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@work_area_seq", work_area_seq);
                dbManager.AddParameters(3, "@bgm_year", bgm_year);

                strSQL = "SELECT r.receive_seq"
                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT(r.receipt_no, ' : รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT(r.receipt_no, ' : 'รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                            + " ELSE CONCAT(r.receipt_no, ' : ', rt.receive_type_name)"
                            + " END"
                        + " END AS receive_type_name"
                    + " FROM fim_receives r"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " WHERE r.record_status not in ('D', 'C')"
                    + (bgm_source_seq != null ? " AND r.bgm_source_seq = @bgm_source_seq" : "")
                    + (work_area_seq != null ? " AND r.work_area_seq = @work_area_seq" : "")
                    + (bgm_year != null ? " AND r.bgm_year = @bgm_year" : "")
                    + " ORDER BY r.receipt_no, r.receive_date";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Receives info = new M_FIM_Receives();
                        info.receive_seq = Convert.ToInt32(drc[i]["receive_seq"]);
                        info.receive_type_name = drc[i]["receive_type_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_Receives GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_Receives info = new M_FIM_Receives();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT r.receive_seq, r.bgm_source_seq, r.work_area_seq, r.bgm_year"
                    + ", r.receive_type_seq, r.receipt_no, r.ref_no, r.recive_remark, r.exclude_vat, r.vat_amount, r.vat_percent, r.include_vat,  r.tax_percent, r.tax"
                    + ", r.receive_date, r.manual_receipt, r.receive_mode, r.method_seq"
                    + ", r.cheque_no, r.branch_seq, r.bank_acc_seq, r.contact_seq"//, r.contact_person_seq"
                    + ", r.emp_seq, r.temp_contact, r.amount"//, r.currency_seq, r.exchange_rate, r.bath_amount"
                    + ", r.received_by, r.remarks, r.tran_grp_seq, r.deposit_seq"
                    + ", r.petition_seq, r.money_req_seq, r.pay_seq, r.record_status"
                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end as payer_name"
                    + ", r.icm_bid_rec_seq, r.icm_bid_off_col_seq, r.icm_po_con_col_seq, r.icm_po_pre_col_seq"
                    + ", r.icm_dn_pen_seq, r.icm_po_instal_seq, r.icm_stockmm_seq"

                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"
                    + ", bb.bank_seq, b.bank_id, b.bank_name, bb.branch_id, bb.branch_name"
                    + ", acc.bank_acc_no, acc.bank_acc_name"

                    + ", s.bgm_source_name, area.work_area_name, c.NameTh"
                    //+ ", cp.contact_person_name"
                    + ", m.method_name"//, cur.currency_name"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", concat(recnp.prefix_name, rec.first_name, ' ', rec.last_name) as received_by_name"
                    + ", rs.record_status_name"
                    + ", r.created_by, r.created_when, r.updated_by, r.updated_when"
                    + " FROM fim_receives r"
                    //+ " LEFT JOIN (select receive_seq"
                    //    + ", sum(coalesce(bath_amount, 0)) as total_bath_amount"
                    //    + " from fim_receive_dtls"
                    //    + " where record_status not like 'D'"
                    //    + " group by receive_seq"
                    //    + ") rd on r.receive_seq = rd.receive_seq"
                    //+ " LEFT JOIN fim_mas_currencies cur ON r.currency_seq = cur.currency_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_mas_methods m ON r.method_seq = m.method_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON r.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON r.bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN bgm_mas_source s ON r.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
                    //+ " LEFT JOIN fim_mas_contact_persons cp ON r.contact_person_seq = cp.contact_person_seq"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_ps_employees rec ON r.received_by = rec.emp_seq"
                    + " LEFT JOIN mas_name_prefixes recnp ON rec.name_prefix_seq = recnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON r.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN sys_record_status rs ON r.record_status = rs.record_status"
                    + " WHERE r.receive_seq = @seq";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.receive_seq = Convert.ToInt32(drc[0]["receive_seq"]);
                    info.bgm_source_seq = cn.ConvertNullInt(drc[0]["bgm_source_seq"]);
                    info.work_area_seq = cn.ConvertNullInt(drc[0]["work_area_seq"]);
                    info.bgm_year = cn.ConvertNullInt(drc[0]["bgm_year"]);
                    info.receive_type_seq = cn.ConvertNullInt(drc[0]["receive_type_seq"]);
                    info.receipt_no = drc[0]["receipt_no"].ToString();
                    info.receive_date = cn.ConvertNullDateTime(drc[0]["receive_date"]);
                    info.manual_receipt = drc[0]["manual_receipt"].ToString();
                    info.receive_mode = drc[0]["receive_mode"].ToString();
                    info.method_seq = cn.ConvertNullInt(drc[0]["method_seq"]);
                    info.cheque_no = drc[0]["cheque_no"].ToString();
                    info.branch_seq = cn.ConvertNullInt(drc[0]["branch_seq"]);
                    info.bank_acc_seq = cn.ConvertNullInt(drc[0]["bank_acc_seq"]);
                    info.contact_seq = cn.ConvertNullInt(drc[0]["contact_seq"]);
                    //info.contact_person_seq = cn.ConvertNullInt(drc[0]["contact_person_seq"]);
                    info.emp_seq = cn.ConvertNullInt(drc[0]["emp_seq"]);
                    info.temp_contact = drc[0]["temp_contact"].ToString();
                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    //info.currency_seq = cn.ConvertNullInt(drc[0]["currency_seq"]);
                    //info.exchange_rate = cn.ConvertNullDouble(drc[0]["exchange_rate"]);
                    //info.bath_amount = cn.ConvertNullDouble(drc[0]["bath_amount"]);
                    info.received_by = cn.ConvertNullInt(drc[0]["received_by"]);
                    info.remarks = drc[0]["remarks"].ToString();
                    info.tran_grp_seq = cn.ConvertNullInt(drc[0]["tran_grp_seq"]);
                    info.deposit_seq = cn.ConvertNullInt(drc[0]["deposit_seq"]);
                    info.petition_seq = cn.ConvertNullInt(drc[0]["petition_seq"]);
                    info.money_req_seq = cn.ConvertNullInt(drc[0]["money_req_seq"]);
                    info.pay_seq = cn.ConvertNullInt(drc[0]["pay_seq"]);
                    info.payer_name = drc[0]["payer_name"].ToString();
                    info.icm_bid_rec_seq = cn.ConvertNullInt(drc[0]["icm_bid_rec_seq"]);
                    info.icm_bid_off_col_seq = cn.ConvertNullInt(drc[0]["icm_bid_off_col_seq"]);
                    info.icm_po_con_col_seq = cn.ConvertNullInt(drc[0]["icm_po_con_col_seq"]);
                    info.icm_po_pre_col_seq = cn.ConvertNullInt(drc[0]["icm_po_pre_col_seq"]);
                    info.icm_dn_pen_seq = cn.ConvertNullInt(drc[0]["icm_dn_pen_seq"]);
                    info.icm_po_instal_seq = cn.ConvertNullInt(drc[0]["icm_po_instal_seq"]);
                    info.icm_stockmm_seq = cn.ConvertNullInt(drc[0]["icm_stockmm_seq"]);
                    info.receive_type_name = drc[0]["receive_type_name"].ToString();
                    info.bgm_source_name = drc[0]["bgm_source_name"].ToString();
                    info.work_area_name = drc[0]["work_area_name"].ToString();
                    info.contact_name = drc[0]["NameTh"].ToString();
                    //info.contact_person_name = drc[0]["contact_person_name"].ToString();
                    info.method_name = drc[0]["method_name"].ToString();
                    //info.currency_name = drc[0]["currency_name"].ToString();
                    info.bank_seq = cn.ConvertNullInt(drc[0]["bank_seq"]);
                    info.bank_id = drc[0]["bank_id"].ToString();
                    info.bank_name = drc[0]["bank_name"].ToString();
                    info.branch_id = drc[0]["branch_id"].ToString();
                    info.branch_name = drc[0]["branch_name"].ToString();
                    info.bank_acc_no = drc[0]["bank_acc_no"].ToString();
                    info.bank_acc_name = drc[0]["bank_acc_name"].ToString();
                    info.emp_name = drc[0]["emp_name"].ToString();
                    info.received_by_name = drc[0]["received_by_name"].ToString();
                    info.recive_remark = drc[0]["recive_remark"].ToString();
                    info.vat_amount = cn.ConvertNullDecimal(drc[0]["vat_amount"]);
                    info.exclude_vat = cn.ConvertNullDecimal(drc[0]["exclude_vat"]);
                    info.vat_percent = cn.ConvertNullDecimal(drc[0]["vat_percent"]);
                    info.tax_percent = cn.ConvertNullDecimal(drc[0]["tax_percent"]);
                    info.tax = cn.ConvertNullDecimal(drc[0]["tax"]);
                    info.include_vat = cn.ConvertNullDecimal(drc[0]["include_vat"]);
                    info.ref_no = (drc[0]["ref_no"]).ToString();

                    info.record_status = drc[0]["RECORD_STATUS"].ToString();
                    info.record_status_name = drc[0]["RECORD_STATUS_NAME"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["CREATED_BY"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["CREATED_WHEN"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["UPDATED_BY"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["UPDATED_WHEN"]); 
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_FIM_Receives GetInfoFromPetition(int petition_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_Receives info = new M_FIM_Receives();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@petition_seq", petition_seq);

                strSQL = "SELECT r.receive_seq, r.bgm_source_seq, r.work_area_seq, r.bgm_year"
                    + ", r.receive_type_seq, r.receipt_no, r.ref_no, r.exclude_vat, r.vat_amount, r.vat_percent, r.include_vat,  r.tax_percent, r.tax"
                    + ", r.receive_date, r.manual_receipt, r.receive_mode, r.method_seq"
                    + ", r.cheque_no, r.branch_seq, r.bank_acc_seq, r.contact_seq"
                    + ", r.emp_seq, r.temp_contact, r.amount" // r.currency_seq, r.exchange_rate, r.bath_amount"
                    + ", r.received_by, r.remarks, r.tran_grp_seq, r.deposit_seq"
                    + ", r.petition_seq, r.money_req_seq, r.pay_seq, r.record_status"
                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end as payer_name"
                    + ", r.icm_bid_rec_seq, r.icm_bid_off_col_seq, r.icm_po_con_col_seq, r.icm_po_pre_col_seq"
                    + ", r.icm_dn_pen_seq, r.icm_po_instal_seq, r.icm_stockmm_seq"

                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"
                    + ", bb.bank_seq, b.bank_id, b.bank_name, bb.branch_id, bb.branch_name"
                    + ", acc.bank_acc_no, acc.bank_acc_name"

                    + ", s.bgm_source_name, area.work_area_name, c.NameTh"
                    + ", m.method_name"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", concat(recnp.prefix_name, rec.first_name, ' ', rec.last_name) as received_by_name"
                    + ", rs.record_status_name"
                    + ", r.created_by, r.created_when, r.updated_by, r.updated_when"
                    + " FROM fim_receives r"
                    //+ " LEFT JOIN fim_mas_currencies cur ON r.currency_seq = cur.currency_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_mas_methods m ON r.method_seq = m.method_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON r.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON r.bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN bgm_mas_source s ON r.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
                    //+ " LEFT JOIN fim_mas_contact_persons cp ON r.contact_person_seq = cp.contact_person_seq"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_ps_employees rec ON r.received_by = rec.emp_seq"
                    + " LEFT JOIN mas_name_prefixes recnp ON rec.name_prefix_seq = recnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON r.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN sys_record_status rs ON r.record_status = rs.record_status"
                    + " WHERE r.petition_seq = @petition_seq"
                    + " and r.record_status not like @delete_status";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.receive_seq = Convert.ToInt32(drc[0]["receive_seq"]);
                    info.bgm_source_seq = cn.ConvertNullInt(drc[0]["bgm_source_seq"]);
                    info.work_area_seq = cn.ConvertNullInt(drc[0]["work_area_seq"]);
                    info.bgm_year = cn.ConvertNullInt(drc[0]["bgm_year"]);
                    info.receive_type_seq = cn.ConvertNullInt(drc[0]["receive_type_seq"]);
                    info.receipt_no = drc[0]["receipt_no"].ToString();
                    info.receive_date = cn.ConvertNullDateTime(drc[0]["receive_date"]);
                    info.manual_receipt = drc[0]["manual_receipt"].ToString();
                    info.receive_mode = drc[0]["receive_mode"].ToString();
                    info.method_seq = cn.ConvertNullInt(drc[0]["method_seq"]);
                    info.cheque_no = drc[0]["cheque_no"].ToString();
                    info.branch_seq = cn.ConvertNullInt(drc[0]["branch_seq"]);
                    info.bank_acc_seq = cn.ConvertNullInt(drc[0]["bank_acc_seq"]);
                    info.contact_seq = cn.ConvertNullInt(drc[0]["contact_seq"]);
                    //info.contact_person_seq = cn.ConvertNullInt(drc[0]["contact_person_seq"]);
                    info.emp_seq = cn.ConvertNullInt(drc[0]["emp_seq"]);
                    info.temp_contact = drc[0]["temp_contact"].ToString();
                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.received_by = cn.ConvertNullInt(drc[0]["received_by"]);
                    info.remarks = drc[0]["remarks"].ToString();
                    info.tran_grp_seq = cn.ConvertNullInt(drc[0]["tran_grp_seq"]);
                    info.deposit_seq = cn.ConvertNullInt(drc[0]["deposit_seq"]);
                    info.petition_seq = cn.ConvertNullInt(drc[0]["petition_seq"]);
                    info.money_req_seq = cn.ConvertNullInt(drc[0]["money_req_seq"]);
                    info.pay_seq = cn.ConvertNullInt(drc[0]["pay_seq"]);
                    info.payer_name = drc[0]["payer_name"].ToString();
                    info.icm_bid_rec_seq = cn.ConvertNullInt(drc[0]["icm_bid_rec_seq"]);
                    info.icm_bid_off_col_seq = cn.ConvertNullInt(drc[0]["icm_bid_off_col_seq"]);
                    info.icm_po_con_col_seq = cn.ConvertNullInt(drc[0]["icm_po_con_col_seq"]);
                    info.icm_po_pre_col_seq = cn.ConvertNullInt(drc[0]["icm_po_pre_col_seq"]);
                    info.icm_dn_pen_seq = cn.ConvertNullInt(drc[0]["icm_dn_pen_seq"]);
                    info.icm_po_instal_seq = cn.ConvertNullInt(drc[0]["icm_po_instal_seq"]);
                    info.icm_stockmm_seq = cn.ConvertNullInt(drc[0]["icm_stockmm_seq"]);
                    info.receive_type_name = drc[0]["receive_type_name"].ToString();
                    info.bgm_source_name = drc[0]["bgm_source_name"].ToString();
                    info.work_area_name = drc[0]["work_area_name"].ToString();
                    info.contact_name = drc[0]["NameTh"].ToString();
                    info.method_name = drc[0]["method_name"].ToString();
                    info.bank_seq = cn.ConvertNullInt(drc[0]["bank_seq"]);
                    info.bank_id = drc[0]["bank_id"].ToString();
                    info.bank_name = drc[0]["bank_name"].ToString();
                    info.branch_id = drc[0]["branch_id"].ToString();
                    info.branch_name = drc[0]["branch_name"].ToString();
                    info.bank_acc_no = drc[0]["bank_acc_no"].ToString();
                    info.bank_acc_name = drc[0]["bank_acc_name"].ToString();
                    info.emp_name = drc[0]["emp_name"].ToString();
                    info.received_by_name = drc[0]["received_by_name"].ToString();

                    info.vat_amount = cn.ConvertNullDecimal(drc[0]["vat_amount"]);
                    info.exclude_vat = cn.ConvertNullDecimal(drc[0]["exclude_vat"]);
                    info.vat_percent = cn.ConvertNullDecimal(drc[0]["vat_percent"]);
                    info.tax_percent = cn.ConvertNullDecimal(drc[0]["tax_percent"]);
                    info.tax = cn.ConvertNullDecimal(drc[0]["tax"]);
                    info.include_vat = cn.ConvertNullDecimal(drc[0]["include_vat"]);
                    info.ref_no = (drc[0]["ref_no"]).ToString();

                    info.record_status = drc[0]["RECORD_STATUS"].ToString();
                    info.record_status_name = drc[0]["RECORD_STATUS_NAME"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["CREATED_BY"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["CREATED_WHEN"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["UPDATED_BY"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["UPDATED_WHEN"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public bool CheckDuplicateReceiptNo(string receipt_no, string receive_seq)
        {
            string strSQL;
            DataTable dtb = null;
            M_FIM_Receives info = new M_FIM_Receives();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@receipt_no", receipt_no);
                dbManager.AddParameters(1, "@receive_seq", receive_seq);

                strSQL = "SELECT receive_seq"
                    + " FROM fim_receives"
                    + " WHERE record_status <> 'D'"
                    + " and receipt_no = @receipt_no"
                    + (!string.IsNullOrEmpty(receive_seq) ? " and receive_seq <> @receive_seq" : "");

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    return true;
                }

                return false;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //public string SelectRecentInsertedRoleSeq()
        //{
        //    string strSQL;
        //    string strResult;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select scope_identity() as last_seq";

        //        strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strResult;
        //}

        public string Insert(M_FIM_Receives info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(54);

                dbManager.AddParameters(0, "@receive_seq", info.receive_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(2, "@work_area_seq", info.work_area_seq);
                dbManager.AddParameters(3, "@bgm_year", info.bgm_year);
                dbManager.AddParameters(4, "@receive_type_seq", info.receive_type_seq);
                dbManager.AddParameters(5, "@receipt_no", info.receipt_no);
                dbManager.AddParameters(6, "@receive_date", info.receive_date);
                dbManager.AddParameters(7, "@manual_receipt", info.manual_receipt);
                dbManager.AddParameters(8, "@receive_mode", info.receive_mode);
                dbManager.AddParameters(9, "@method_seq", info.method_seq);                
                dbManager.AddParameters(10, "@cheque_no", info.cheque_no);
                dbManager.AddParameters(11, "@branch_seq", info.branch_seq);
                dbManager.AddParameters(12, "@bank_acc_seq", info.bank_acc_seq);
                dbManager.AddParameters(13, "@contact_seq", info.contact_seq);
                dbManager.AddParameters(14, "@contact_person_seq", info.contact_person_seq);
                dbManager.AddParameters(15, "@emp_seq", info.emp_seq);
                dbManager.AddParameters(16, "@temp_contact", info.temp_contact);
                dbManager.AddParameters(17, "@amount", info.amount ?? 0);
                dbManager.AddParameters(18, "@currency_seq", "");
                dbManager.AddParameters(19, "@exchange_rate", "");
                dbManager.AddParameters(20, "@bath_amount", "");
                dbManager.AddParameters(21, "@received_by", info.received_by);
                dbManager.AddParameters(22, "@remarks", info.remarks);
                dbManager.AddParameters(23, "@tran_grp_seq", info.tran_grp_seq);
                dbManager.AddParameters(24, "@deposit_seq", info.deposit_seq);
                dbManager.AddParameters(25, "@petition_seq", info.petition_seq);
                dbManager.AddParameters(26, "@money_req_seq", info.money_req_seq);
                dbManager.AddParameters(27, "@icm_bid_rec_seq", info.icm_bid_rec_seq);
                dbManager.AddParameters(28, "@icm_bid_off_col_seq", info.icm_bid_off_col_seq);
                dbManager.AddParameters(29, "@icm_po_con_col_seq", info.icm_po_con_col_seq);
                dbManager.AddParameters(30, "@icm_po_pre_col_seq", info.icm_po_pre_col_seq);
                dbManager.AddParameters(31, "@icm_dn_pen_seq", info.icm_dn_pen_seq);
                dbManager.AddParameters(32, "@icm_po_instal_seq", info.icm_po_instal_seq);
                dbManager.AddParameters(33, "@icm_stockmm_seq", info.icm_stockmm_seq);

                dbManager.AddParameters(34, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(35, "@created_by", info.created_by);
                dbManager.AddParameters(36, "@created_when", info.created_when);
                dbManager.AddParameters(37, "@vat_amount", info.vat_amount ?? 0);
                dbManager.AddParameters(38, "@exclude_vat", info.exclude_vat ?? 0);
                dbManager.AddParameters(39, "@vat_percent", info.vat_percent ?? 0);
                dbManager.AddParameters(40, "@recive_remark", info.recive_remark);
                dbManager.AddParameters(41, "@tax", info.tax ?? 0);
                dbManager.AddParameters(42, "@tax_percent", info.tax_percent ?? 0);
                dbManager.AddParameters(43, "@include_vat", info.include_vat ?? 0);
                dbManager.AddParameters(44, "@ref_no", info.ref_no);
                dbManager.AddParameters(45, "@contact_address", info.contact_address);
                dbManager.AddParameters(46, "@source", info.source);
                dbManager.AddParameters(47, "@print_out", info.print_out);
                dbManager.AddParameters(48, "@contact_branch", info.contact_branch);
                dbManager.AddParameters(49, "@contact_tax_code", info.contact_tax_code);
                dbManager.AddParameters(50, "@qty", info.qty);
                dbManager.AddParameters(51, "@discount_percent", info.discount_percent);
                dbManager.AddParameters(52, "@discount", info.discount);
                dbManager.AddParameters(53, "@unit_price", info.unit_price);

                strSQL = "INSERT INTO fim_receives"
                    + "(bgm_source_seq, work_area_seq, bgm_year, receive_type_seq"
                    + ", receipt_no, ref_no, receive_date, manual_receipt, receive_mode"
                    + ", method_seq, cheque_no, branch_seq, bank_acc_seq, contact_seq"
                    + ", emp_seq, temp_contact, amount, vat_amount, exclude_vat, vat_percent, include_vat" //, currency_seq, exchange_rate, bath_amount" 
                    + ", received_by, remarks, tran_grp_seq"
                    + ", deposit_seq, petition_seq, money_req_seq"
                    + ", icm_bid_rec_seq, icm_bid_off_col_seq, icm_po_con_col_seq"
                    + ", icm_po_pre_col_seq, icm_dn_pen_seq, icm_po_instal_seq, icm_stockmm_seq"
                    + ", recive_remark, tax, tax_percent, contact_address, source, print_out, "
                    + " contact_branch, contact_tax_code, record_status, created_by, created_when,qty,discount_percent,discount,unit_price)"
                    + " VALUES(@bgm_source_seq"
                        + ", @work_area_seq"
                        + ", @bgm_year"
                        + ", @receive_type_seq"
                        + ", @receipt_no"
                        + ", @ref_no"
                        + ", @receive_date"
                        + ", @manual_receipt"
                        + ", @receive_mode"
                        + ", @method_seq"
                        + ", @cheque_no"
                        + ", @branch_seq"
                        + ", @bank_acc_seq"
                        + ", @contact_seq"
                        + ", @emp_seq"
                        + ", @temp_contact"
                        + ", @amount"
                        + ", @vat_amount"
                        + ", @exclude_vat"
                        + ", @vat_percent"
                        + ", @include_vat"
                        //+ ", @currency_seq"
                        //+ ", @exchange_rate"
                        //+ ", @bath_amount"
                        + ", @received_by"
                        + ", @remarks"
                        + ", @tran_grp_seq"
                        + ", @deposit_seq"
                        + ", @petition_seq"
                        + ", @money_req_seq"
                        + ", @icm_bid_rec_seq"
                        + ", @icm_bid_off_col_seq"
                        + ", @icm_po_con_col_seq"
                        + ", @icm_po_pre_col_seq"
                        + ", @icm_dn_pen_seq"
                        + ", @icm_po_instal_seq"
                        + ", @icm_stockmm_seq"
                        + ", @recive_remark"
                        + ", @tax"
                        + ", @tax_percent"
                        + ", @contact_address"
                        + ", @source"
                        + ", @print_out"
                        + ", @contact_branch"
                        + ", @contact_tax_code"
                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when"
                        + ", @qty"
                        + ", @discount_percent"
                        + ", @discount"
                        + ", @unit_price);"
                    + " select scope_identity();";
                var strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                return strSeq;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_Receives info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(51);

                dbManager.AddParameters(0, "@receive_seq", info.receive_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(2, "@work_area_seq", info.work_area_seq);
                dbManager.AddParameters(3, "@bgm_year", info.bgm_year);
                dbManager.AddParameters(4, "@receive_type_seq", info.receive_type_seq);
                dbManager.AddParameters(5, "@receipt_no", info.receipt_no);
                dbManager.AddParameters(6, "@receive_date", info.receive_date);
                dbManager.AddParameters(7, "@manual_receipt", info.manual_receipt);
                dbManager.AddParameters(8, "@receive_mode", info.receive_mode);
                dbManager.AddParameters(9, "@method_seq", info.method_seq);
                dbManager.AddParameters(10, "@cheque_no", info.cheque_no);
                dbManager.AddParameters(11, "@branch_seq", info.branch_seq);
                dbManager.AddParameters(12, "@bank_acc_seq", info.bank_acc_seq);
                dbManager.AddParameters(13, "@contact_seq", info.contact_seq);
                dbManager.AddParameters(14, "@contact_person_seq", info.contact_person_seq);
                dbManager.AddParameters(15, "@emp_seq", info.emp_seq);
                dbManager.AddParameters(16, "@temp_contact", info.temp_contact);
                dbManager.AddParameters(17, "@amount", info.amount);
                dbManager.AddParameters(18, "@currency_seq", info.currency_seq);
                dbManager.AddParameters(19, "@exchange_rate", info.exchange_rate);
                dbManager.AddParameters(20, "@bath_amount", info.bath_amount);
                dbManager.AddParameters(21, "@received_by", info.received_by);
                dbManager.AddParameters(22, "@remarks", info.remarks);
                dbManager.AddParameters(23, "@tran_grp_seq", info.tran_grp_seq);
                dbManager.AddParameters(24, "@deposit_seq", info.deposit_seq);
                dbManager.AddParameters(25, "@petition_seq", info.petition_seq);
                dbManager.AddParameters(26, "@money_req_seq", info.money_req_seq);
                dbManager.AddParameters(27, "@icm_bid_rec_seq", info.icm_bid_rec_seq);
                dbManager.AddParameters(28, "@icm_bid_off_col_seq", info.icm_bid_off_col_seq);
                dbManager.AddParameters(29, "@icm_po_con_col_seq", info.icm_po_con_col_seq);
                dbManager.AddParameters(30, "@icm_po_pre_col_seq", info.icm_po_pre_col_seq);
                dbManager.AddParameters(31, "@icm_dn_pen_seq", info.icm_dn_pen_seq);
                dbManager.AddParameters(32, "@icm_po_instal_seq", info.icm_po_instal_seq);
                dbManager.AddParameters(33, "@icm_stockmm_seq", info.icm_stockmm_seq);

                dbManager.AddParameters(34, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(35, "@updated_by", info.updated_by);
                dbManager.AddParameters(36, "@updated_when", info.updated_when);
                dbManager.AddParameters(37, "@vat_amount", info.vat_amount);
                dbManager.AddParameters(38, "@exclude_vat", info.exclude_vat);
                dbManager.AddParameters(39, "@vat_percent", info.vat_percent);
                dbManager.AddParameters(40, "@recive_remark", info.recive_remark);
                dbManager.AddParameters(41, "@tax", info.tax);
                dbManager.AddParameters(42, "@tax_percent", info.tax_percent);
                dbManager.AddParameters(43, "@include_vat", info.include_vat);
                dbManager.AddParameters(44, "@ref_no", info.ref_no);
                dbManager.AddParameters(45, "@qty", info.qty);
                dbManager.AddParameters(46, "@discount_percent", info.discount_percent);
                dbManager.AddParameters(47, "@discount", info.discount);
                dbManager.AddParameters(48, "@unit_price", info.unit_price);
                dbManager.AddParameters(49, "@created_when", info.created_when); //เก็บวันที่สร้างใบเสร็จ
                dbManager.AddParameters(50, "@contact_address", info.contact_address);

                strSQL = "UPDATE fim_receives"
                    + " set bgm_source_seq = @bgm_source_seq"
                    + ", work_area_seq = @work_area_seq"
                    + ", bgm_year = @bgm_year"
                    + ", receive_type_seq = @receive_type_seq"
                    + ", receipt_no = @receipt_no"
                    + ", ref_no = @ref_no"
                    + ", receive_date = @receive_date"
                    + ", manual_receipt = @manual_receipt"
                    + ", receive_mode = @receive_mode"
                    + ", method_seq = @method_seq"
                    + ", cheque_no = @cheque_no"
                    + ", branch_seq = @branch_seq"
                    + ", bank_acc_seq = @bank_acc_seq"
                    + ", contact_seq = @contact_seq"
                    + ", contact_person_seq = @contact_person_seq"
                    + ", emp_seq = @emp_seq"
                    + ", temp_contact = @temp_contact"
                    + ", amount = @amount"
                    + ", vat_amount = @vat_amount"
                    + ", vat_percent = @vat_percent"
                    + ", exclude_vat = @exclude_vat"
                    + ", currency_seq = @currency_seq"
                    + ", exchange_rate = @exchange_rate"
                    + ", bath_amount = @bath_amount"
                    + ", received_by = @received_by"
                    + ", remarks = @remarks"
                    + ", petition_seq = @petition_seq"
                    + ", money_req_seq = @money_req_seq"
                    + ", icm_bid_rec_seq = @icm_bid_rec_seq"
                    + ", icm_bid_off_col_seq = @icm_bid_off_col_seq"
                    + ", icm_po_con_col_seq = @icm_po_con_col_seq"
                    + ", icm_po_pre_col_seq = @icm_po_pre_col_seq"
                    + ", icm_dn_pen_seq = @icm_dn_pen_seq"
                    + ", icm_po_instal_seq = @icm_po_instal_seq"
                    + ", icm_stockmm_seq = @icm_stockmm_seq"
                    + ", recive_remark = @recive_remark"
                    + ", tax = @tax"
                    + ", tax_percent = @tax_percent"
                    + ", include_vat = @include_vat"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + ", qty = @qty"
                    + ", discount_percent = @discount_percent"
                    + ", discount = @discount"
                    + ", unit_price = @unit_price"
                    + ", created_when = @created_when"
                    + ", contact_address = @contact_address"
                    + " WHERE receive_seq = @receive_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_Receives info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@receive_seq", info.receive_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_receives"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE receive_seq = @receive_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateGlTranSeq(M_FIM_Receives info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@receive_seq", info.receive_seq);
                dbManager.AddParameters(1, "@tran_grp_seq", info.tran_grp_seq);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_receives"
                    + " SET tran_grp_seq = @tran_grp_seq"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE receive_seq = @receive_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateDepositSeq(M_FIM_Receives info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@receive_seq", info.receive_seq);
                dbManager.AddParameters(1, "@deposit_seq", info.deposit_seq);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_receives"
                    + " SET deposit_seq = @deposit_seq"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE receive_seq = @receive_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateReceiptNo(M_FIM_Receives info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@receive_seq", info.receive_seq);
                dbManager.AddParameters(1, "@receipt_no", info.receipt_no);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);
                dbManager.AddParameters(4, "@approve_status", SystemInfo._ApproveRecordStatus);

                strSQL = "UPDATE fim_receives"
                    + " SET receipt_no = @receipt_no"
                    + ", record_status = @approve_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE receive_seq = @receive_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteDepositSeq(M_FIM_Receives info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@deposit_seq", info.deposit_seq);
                dbManager.AddParameters(1, "@updated_by", info.updated_by);
                dbManager.AddParameters(2, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_receives"
                    + " SET deposit_seq = null"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE deposit_seq = @deposit_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdatePaySeq(M_FIM_Receives info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@receive_seq", info.receive_seq);
                dbManager.AddParameters(1, "@pay_seq", info.pay_seq);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_receives"
                    + " SET pay_seq = @pay_seq"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE receive_seq = @receive_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable Report_Receipt(int seq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT r.receive_seq, r.bgm_source_seq, r.bgm_year, r.receive_type_seq, r.receipt_no"
                    + ", r.receive_date, r.manual_receipt"
                    + ", r.receive_mode, r.method_seq, r.contact_seq"
                    + ", r.emp_seq, r.temp_contact, r.amount"
                    + ", r.received_by, r.remarks, r.tran_grp_seq, r.deposit_seq"
                    + ", r.petition_seq, r.money_req_seq, r.pay_seq, r.record_status"
                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end as payer_name"
                    + ", r.icm_bid_rec_seq, r.icm_bid_off_col_seq, r.icm_po_con_col_seq, r.icm_po_pre_col_seq"
                    + ", r.icm_dn_pen_seq, r.icm_po_instal_seq, r.icm_stockmm_seq"

                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"

                    + ", s.bgm_source_name, c.NameTh, m.method_name"
                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", concat(recnp.prefix_name, rec.first_name, ' ', rec.last_name) as received_by_name"
                    + ", rs.record_status_name"
                    + ", r.created_by, r.created_when, r.updated_by, r.updated_when"
                    + " FROM fim_receives r"
                    //+ " LEFT JOIN (select receive_seq"
                    //    + ", sum(coalesce(bath_amount, 0)) as total_bath_amount"
                    //    + " from fim_receive_dtls"
                    //    + " where record_status not like 'D'"
                    //    + " group by receive_seq"
                    //    + ") rd on r.receive_seq = rd.receive_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_mas_methods m ON r.method_seq = m.method_seq"
                    + " LEFT JOIN bgm_mas_source s ON r.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c. ConId"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_ps_employees rec ON r.received_by = rec.emp_seq"
                    + " LEFT JOIN mas_name_prefixes recnp ON rec.name_prefix_seq = recnp.name_prefix_seq"
                    + " LEFT JOIN sys_record_status rs ON r.record_status = rs.record_status"
                    + " WHERE r.receive_seq = @seq";
                

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable Report_SumUpReceive(Nullable<DateTime> date)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@date", date);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT r.receipt_no, r.cheque_no, r.amount"
                    + ", r.receive_date"
                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end as payer_name"
                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"

                    + " FROM fim_receives r"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c. ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " WHERE r.receive_date = @date";
                

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable Report_SumUpReceiveFT(Nullable<DateTime> dateFrom, Nullable<DateTime> dateTo, string bgm_source_seq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@from_date", dateFrom);
                dbManager.AddParameters(1, "@to_date", dateTo);
                dbManager.AddParameters(2, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@bgm_source_seq", bgm_source_seq);

                strSQL = "SELECT r.receive_date, r.ref_no, r.receipt_no"

                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end as payer_name"
                    + ", r.cheque_no"
                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"
                    + ", r.amount, r.remarks"
                    
                    //+ ", acc.bank_acc_seq, acc.bank_acc_no, acc.bank_acc_name"
                    //+ ", bb.branch_seq, bb.branch_id, bb.branch_name"
                    //+ ", b.bank_seq, b.bank_id, b.bank_name"

                    + " FROM fim_receives r"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c. ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp on emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_transfers tf on r.deposit_seq = tf.transfer_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc on tf.to_bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb on acc.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b on bb.bank_seq = b.bank_seq"
                    + " WHERE r.record_status not like @record_status"
                    + (bgm_source_seq != "" ? " and r.bgm_source_seq like @bgm_source_seq" : "")
                    + (dateFrom != null ? " and r.receive_date >= @from_date" : "")
                    + (dateTo != null ? " and r.receive_date <= @to_date" : "")
                    + " order by r.receive_date, r.receipt_no";
                

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable Report_SumUpReceiveFT2(Nullable<DateTime> dateFrom, Nullable<DateTime> dateTo, string bgm_source_seq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@from_date", dateFrom);
                dbManager.AddParameters(1, "@to_date", dateTo);
                dbManager.AddParameters(2, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@bgm_source_seq", bgm_source_seq);


                strSQL = "SELECT r.receive_date, r.ref_no, r.receipt_no"

                    + ", case when coalesce(r.temp_contact, '') <> '' then r.temp_contact"
                        + " else case when r.contact_seq is not null then c.NameTh"
                            + " else concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                            + " end"
                        + " end as payer_name"
                    + ", r.cheque_no"
                    + ", CASE WHEN r.receive_mode = '1' THEN CONCAT('รับเงินคืนจากรายการรอเบิกที่ ', mr.ref_no)"
                        + " ELSE CASE WHEN r.receive_mode = '3' THEN CONCAT('รับเงินจากรายการขอเบิกที่ ', pt.ref_no)"
                            + " ELSE rt.receive_type_name"
                            + " END"
                        + " END AS receive_type_name"
                    + ", r.amount, r.remarks"

                    + ", acc.bank_acc_seq, acc.bank_acc_no, acc.bank_acc_name"
                    + ", bb.branch_seq, bb.branch_id, bb.branch_name"
                    + ", b.bank_seq, b.bank_id, b.bank_name"

                    + " FROM fim_receives r"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c. ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp on emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " LEFT JOIN fim_transfers tf on r.deposit_seq = tf.transfer_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc on tf.to_bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb on acc.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b on bb.bank_seq = b.bank_seq"
                    + " WHERE r.record_status not like @record_status"
                    + (bgm_source_seq != "" ? " and r.bgm_source_seq like @bgm_source_seq" : "")
                    + (dateFrom != null ? " and r.receive_date >= @from_date" : "")
                    + (dateTo != null ? " and r.receive_date <= @to_date" : "")
                    + " order by bb.branch_seq, r.receive_date, r.receipt_no";
                

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable PrintCheqe(DateTime RecieveDate)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@receive_date", RecieveDate);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT *"
                    + " FROM fim_receives r"
                    + " LEFT JOIN IcmMasContact c ON r.contact_seq = c. ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    + " LEFT JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " LEFT JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " LEFT JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " WHERE r.receive_date = @receive_date AND r.record_status not like @record_status";


                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable PrintHAInvoice(DateTime ReceiveDate)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@receive_date", ReceiveDate);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT *"
                    + " FROM IcmMasContact c"
                    + " FULL OUTER JOIN fim_receives r ON r.contact_seq = c. ConId"
                    + " FULL OUTER JOIN hrm_ps_employees emp ON r.emp_seq = emp.emp_seq"
                    + " FULL OUTER JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    + " FULL OUTER JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " FULL OUTER JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " FULL OUTER JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " WHERE r.receive_date = @receive_date AND r.record_status not like @record_status";


                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable PrintHAInvoice(int ReceiveSeq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@receive_seq", ReceiveSeq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT receive_seq,temp_contact,contact_branch,contact_address,receive_date,r.remarks,exclude_vat,vat_amount,amount,cheque_no,np.prefix_name+emp.first_name+' '+emp.last_name as fullemp_name,pos_name,branch_name,bank_name"
                    + " FROM IcmMasContact c"
                    + " FULL OUTER JOIN fim_receives r ON r.contact_seq = c. ConId"
                    + " FULL OUTER JOIN hrm_ps_employees emp ON r.created_by = emp.emp_seq"
                    + " FULL OUTER JOIN mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq"
                    + " FULL OUTER JOIN fim_money_requests mr ON r.money_req_seq = mr.money_req_seq"
                    + " FULL OUTER JOIN fim_petitions pt ON r.petition_seq = pt.petition_seq"
                    + " FULL OUTER JOIN fim_mas_receive_types rt ON r.receive_type_seq = rt.receive_type_seq"
                    + " FULL OUTER JOIN hrm_mp_positions pos ON emp.pos_seq = pos.pos_seq"
                    + " FULL OUTER JOIN fim_mas_bank_branchs bkb ON r.branch_seq = bkb.branch_seq"
                    + " FULL OUTER JOIN fim_mas_banks bk ON bkb.bank_seq = bk.bank_seq"
                    + " WHERE r.receive_seq = @receive_seq AND r.record_status not like @record_status";


                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public void UpdatePrintOut(int updated_by, DateTime? receive_date, int print_out, string receive_seq)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@updated_by", updated_by);
                dbManager.AddParameters(1, "@receive_date", receive_date);
                dbManager.AddParameters(2, "@print_out", print_out);
                dbManager.AddParameters(3, "@receive_seq", receive_seq);

                strSQL = " UPDATE fim_receives"
                    + " SET print_out = @print_out"
                    + ", updated_by = @updated_by"
                    + ", updated_when = GETDATE()"
                    + " WHERE record_status not like 'D'"
                 + (receive_date != null ? " AND receive_date = @receive_date" : "")
                 + (receive_seq != null ? " AND receive_seq = @receive_seq" : "");


                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

    }
}